Re: index file bloating still in 7.4 ? - Mailing list pgsql-performance

Hi Josh, Tom,

OK. As I understand it, vacuum does not release the space
used by the index file.
However, it should be able to reuse the space for indexing.

I have observed that during initial updates of the table,
the index file did not grow and was steady but it did not last long
and keeps growing afterwards. Vacuum/vacuum analyze did not help.

In all the update testing, vacuum analyze was done every 1 minute.

Tom, something caught your attention the last time.

Any insight so far ? Is it a bug ?

Thanks.

Gan

Tom Lane wrote:

Seum-Lim Gan <slgan@lucent.com> writes:
>  vacuum verbose analyze dsperf_rda_or_key;
>  INFO:  vacuuming "scncraft.dsperf_rda_or_key"
>  INFO:  index "dsperf242_1105" now contains 300000 row versions in 12387 pages
>  DETAIL:  3097702 index row versions were removed.
>  0 index pages have been deleted, 0 are currently reusable.

Hm, interesting that you deleted 90% of the entries and still had no
empty index pages at all.  What was the pattern of your deletes and/or
updates with respect to this index's key?

>  However, when I check the disk space usage, it has not changed.

It won't in any case.  Plain VACUUM is designed for maintaining a
steady-state level of free space in tables and indexes, not for
returning major amounts of space to the OS.  For that you need
more-invasive operations like VACUUM FULL or REINDEX.

            regards, tom lane

At 12:04 pm -0700 2003/10/19, Josh Berkus wrote:
>Gan,
>
>>  Oh, so in order to reclaim the disk space, we must run
>>  reindex or vacuum full ?
>>  This will lock out the table and we won't be able to do anything.
>>  Looks like this is a problem. It means we cannot use it for
>>  24x7 operations without having to stop the process and do the vacuum full
>>  and reindex. Is there anything down the road that these operations
>>  will not lock out the table ?
>
>I doubt it; the amount of page-shuffling required to reclaim 90% of the space
>in an index for a table that has been mostly cleared is substantial, and
>would prevent concurrent access.
>
>Also, you seem to have set up an impossible situation for VACUUM.   If I'm
>reading your statistics right, you have a large number of threads accessing
>most of the data 100% of the time, preventing VACUUM from cleaning up the
>pages.    This is not, in my experience, a realistic test case ... there are
>peak and idle periods for all databases, even webservers that have been
>slashdotted.
>
>--
>Josh Berkus
>Aglio Database Solutions
>San Francisco
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


--
+--------------------------------------------------------+
| Seum-Lim GAN                 email : slgan@lucent.com  |
| Lucent Technologies                                    |
| 2000 N. Naperville Road, 6B-403F  tel : (630)-713-6665 |
| Naperville, IL 60566, USA.        fax : (630)-713-7272 |
|       web : http://inuweb.ih.lucent.com/~slgan         |
+--------------------------------------------------------+